﻿'Description:批次分批
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:tianFW
'create Date:2017-5-20
'Rewriter:
'Rewrite Date:
Imports BusinessRule.DTO
Imports BusinessRule.IBusiness
Imports DatabaseAccess.DBUtility
Imports System.Text
Imports System.Data.OracleClient

Public Class uLMSSplitBusiness
    Implements uLMSSplitIBusiness
    ''' <summary>
    ''' 获取分批审理数据
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSSplitIBusiness.GetContainerInfo
        'Dim dt As DataTable
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT V.MFGORDERNAME,SUBSTR(V.CONTAINERNAME,INSTR(V.CONTAINERNAME,'/')+1) TOGETHERFLAG,V.CONTAINERID,V.CONTAINERNAME,V.PRODUCTNAME,V.QTY,V.WORKFLOWSTEPNAME,V.WORKFLOWSTEPID,V.WORKFLOWSTEPSEQUENCE SEQUENCE," &
                          "DECODE(V.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME, V.PLANNEDSTARTDATE, V.PLANNEDCOMPLETIONDATE,V.CONTAINERTYPE,V.ISHOLD")
        If para("Status") = "1" Then
            strsql.AppendLine(",NVL(V.WORKREPORTNUM,0) WORKREPORTNUM,NVL(V.ALLLOSSQTY,0) ALLLOSSQTY")
            strsql.AppendLine("FROM CONTAINERCURRENTINFO V")
            strsql.AppendLine("WHERE V.CONTAINERTYPE <> '7' AND V.SPLITNUM = 0 ")
        Else
            strsql.AppendLine(",CSI.SPLITCONTAINERNAME,CSI.QTY AS CHILDQTY,CSI.SPLITREASON,CSI.CONTAINERSPLITINFOID,CSI.SPLITRESULT")
            If para("Status") = "2" Then
                strsql.AppendLine(",CSI.WORKFLOWSTEPID CSIWORKFLOWSTEPID,WS.WORKFLOWSTEPNAME CSIWORKFLOWSTEPNAME")
                strsql.AppendLine("FROM CONTAINERSPLITINFO CSI ")
                strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSI.WORKFLOWSTEPID ")
                strsql.AppendLine("LEFT JOIN CONTAINERCURRENTINFO V ON CSI.CONTAINERID = V.CONTAINERID AND V.CONTAINERID IS NOT NULL ")
                strsql.AppendLine("WHERE V.CONTAINERTYPE <> '7' AND CSI.SPLITCONTAINERNAME IS NOT NULL")
            ElseIf para("Status") = "3" Then
                strsql.AppendLine(",CON.ZBHGPS,NVL(V.WORKREPORTNUM,0) WORKREPORTNUM,NVL(V.ALLLOSSQTY,0) ALLLOSSQTY,V.WORKFLOWID,NVL(V.NONCONFORMINGNUM,0) NONCONFORMINGNUM")
                strsql.AppendLine(",V.WORKFLOWNAME,V.WORKFLOWREVISION,V.ORIGINALFACTORYID,V.FACTORYID,V.ISSHIP,V.SHIPNUM,V.SHIPID")
                strsql.AppendLine(",CSI.WORKFLOWSTEPID CSIWORKFLOWSTEPID,WS.WORKFLOWSTEPNAME CSIWORKFLOWSTEPNAME")
                strsql.AppendLine("FROM CONTAINERSPLITINFO CSI ")
                strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSI.WORKFLOWSTEPID ")
                strsql.AppendLine("LEFT JOIN CONTAINERCURRENTINFO V ON CSI.CONTAINERID = V.CONTAINERID  AND V.CONTAINERID IS NOT NULL ")
                strsql.AppendLine("LEFT JOIN CONTAINERNONCONFORMINGINFO CON ON CON.CONTAINERID = V.CONTAINERID AND CON.WORKFLOWSTEPID = V.WORKFLOWSTEPID")
                strsql.AppendLine("WHERE V.CONTAINERTYPE <> '7' AND CSI.SPLITCONTAINERNAME IS NOT NULL AND CSI.SPLITRESULT IS NULL")
            Else
                strsql.AppendLine(",CSI.WORKFLOWSTEPID CSIWORKFLOWSTEPID,WS.WORKFLOWSTEPNAME CSIWORKFLOWSTEPNAME ") 'add:Wangjh 20190226
                strsql.AppendLine("FROM CONTAINERSPLITINFO CSI")
                strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSI.WORKFLOWSTEPID ")
                strsql.AppendLine("LEFT JOIN CONTAINERCURRENTINFO V ON CSI.CONTAINERID = V.CONTAINERID  AND")
                strsql.AppendLine("V.CONTAINERID IS NOT NULL WHERE V.CONTAINERTYPE <> '7' AND CSI.SPLITRESULT IS NOT NULL")
            End If
            strsql.AppendLine(" AND CSI.ISUSE=0") 'modify 2018年6月20日 AND CSI.WORKFLOWSTEPID = V.WORKFLOWSTEPID 申请分批的工序与当前序不相同时也可以查出，但是不能操作分批
        End If
        strsql.AppendFormat(" AND V.ORIGINALFACTORYID='{0}'", para("FactoryID")) '2018年2月5日 按批次分厂查询，及转工就不能分批
        If para.Keys.Contains("ContainerName") Then
            strsql.AppendFormat(" AND V.CONTAINERNAME='{0}'", para("ContainerName"))
        Else
            If para.Keys.Contains("ContainerName_like") Then
                strsql.AppendFormat(" AND V.CONTAINERNAME LIKE '%{0}%'", para("ContainerName_like"))
            End If
        End If
        If para.Keys.Contains("WorkflowStepName") Then
            strsql.AppendFormat(" AND V.SPECNO LIKE '%{0}%'", para("WorkflowStepName"))
        End If
        If para.Keys.Contains("ProductName") Then
            strsql.AppendFormat(" AND V.PRODUCTNAME LIKE '%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("mfgOrderName") Then
            strsql.AppendFormat(" AND V.MFGORDERNAME LIKE '%{0}%'", para("mfgOrderName"))
        End If
        If para.Keys.Contains("CheckEmployeeID") Then
            strsql.AppendFormat(" AND CSI.CHECKEMPLOYEEID = '{0}'", para("CheckEmployeeID"))
        End If
        strsql.AppendLine(" AND V.CONTAINERSTATUS=1 AND V.ISINSTORE=0") 'add 2017/11/02 'add 2017年11月29日
        If para("Status") = "1" Then
            strsql.AppendLine(" AND V.QTY > 1 ORDER BY V.CONTAINERNAME")
        Else
            strsql.AppendLine(" ORDER BY CSI.CREATEDATE DESC")
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strsql.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
        'dt = DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
        'Return dt
    End Function

    ''' <summary>
    ''' 添加批次分批信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AddChildContianerInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSSplitIBusiness.AddChildContianerInfo
        Dim stepDt As DataTable = DatabaseHelper.Query_ReturnDatTable("select WorkflowStepName,Sequence from workflowstep where workflowstepid='" + para("WorkflowStepID") + "'")
        If stepDt.Rows.Count = 0 Then
            Return False
        End If
        Dim strsql As String =
            <string>
                INSERT INTO CONTAINERSPLITINFO(CONTAINERID,WORKFLOWSTEPID,SPLITCONTAINERNAME,SPLITREASON,SPLITEMPLOYEEID,CREATEDATE,QTY,ISUSE,CONTAINERSPLITINFOID,ISTRANSFER,CHECKEMPLOYEEID,WorkflowStepName,Sequence)
                VALUES(:CONTAINERID,:WORKFLOWSTEPID,:SPLITCONTAINERNAME,:SPLITREASON,:SPLITEMPLOYEEID,SYSDATE,:QTY,0,:CONTAINERSPLITINFOID,0,:CHECKEMPLOYEEID,'{0}',{1})
            </string>.Value
        strsql = String.Format(strsql, stepDt.Rows(0)("WorkflowStepName").ToString, stepDt.Rows(0)("Sequence"))
        Dim insertpara(7) As OracleParameter
        insertpara(0) = DatabaseHelper.NewParameter(":CONTAINERID", para("ContainerID"), OracleType.VarChar)
        insertpara(1) = DatabaseHelper.NewParameter(":WORKFLOWSTEPID", para("WorkflowStepID"), OracleType.VarChar)
        insertpara(2) = DatabaseHelper.NewParameter(":SPLITCONTAINERNAME", para("SplitContainerName"), OracleType.VarChar)
        insertpara(3) = DatabaseHelper.NewParameter(":SPLITREASON", para("SplitReason"), OracleType.VarChar)
        insertpara(4) = DatabaseHelper.NewParameter(":SPLITEMPLOYEEID", para("SplitEmployeeID"), OracleType.VarChar)
        insertpara(5) = DatabaseHelper.NewParameter(":QTY", para("Qty"), OracleType.VarChar)
        insertpara(6) = DatabaseHelper.NewParameter(":CONTAINERSPLITINFOID", para("ContainerSplitInfoID"), OracleType.VarChar)
        insertpara(7) = DatabaseHelper.NewParameter(":CHECKEMPLOYEEID", para("CheckEmployeeID"), OracleType.VarChar)
        Dim row As Int32 = DatabaseHelper.ExecuteSql(strsql, insertpara)
        If row > 0 Then
            AddChildContianerInfo = True
        Else
            AddChildContianerInfo = False
        End If
    End Function

    ''' <summary>
    ''' 根据containername获取相似的containername
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetLikeContainerNameByContainerName(ByVal strcontainername) As DataTable Implements IBusiness.uLMSSplitIBusiness.GetLikeContainerNameByContainerName
        Dim strsql As String = String.Format("SELECT MAX(CONTAINERNAME) AS　CONTAINERNAME FROM CONTAINER C WHERE C.CONTAINERNAME LIKE '{0}_'", strcontainername)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function

    ''' <summary>
    ''' 添加检验结果
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AddCheckResult(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSSplitIBusiness.AddCheckResult
        AddCheckResult = False
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE CONTAINERSPLITINFO C SET  C.CHECKDATA = SYSDATE,C.ISTRANSFER=0,")
        strsql.AppendFormat(" C.CHECKEMPLOYEEID = '{0}',", para("EmployID"))
        If para("Key") = "YES" Then
            strsql.AppendFormat(" C.SPLITRESULT= {0}", 0)
        ElseIf para("Key") = "NO" Then
            strsql.AppendFormat(" C.SPLITRESULT= {0}", 1)
        End If
        strsql.AppendFormat(" WHERE C.CONTAINERSPLITINFOID='{0}'", para("ContainerSplitInfoID"))
        Dim row As Integer = DatabaseHelper.ExecuteSql(strsql.ToString)
        If row > 0 Then
            AddCheckResult = True
        End If
    End Function

    ''' <summary>
    ''' 添加分批次明细
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AddChildContainerdetailInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSSplitIBusiness.AddChildContainerdetailInfo
        AddChildContainerdetailInfo = False
        Dim strsql As String =
            <string>
                INSERT INTO CONTAINERSPLITINFODETAIL(CONTAINERSPLITINFOID, CHILDCONTAINERID, CHILDCONTAINERNAME,containerstepworkreportid)
                VALUES(:CONTAINERSPLITINFOID, :CHILDCONTAINERID, :CHILDCONTAINERNAME,:containerstepworkreportid)
            </string>
        Dim insertpara(3) As OracleParameter
        insertpara(0) = DatabaseHelper.NewParameter(":CONTAINERSPLITINFOID", para("ContainerSplitInfoID"), OracleType.VarChar)
        insertpara(1) = DatabaseHelper.NewParameter(":CHILDCONTAINERID", para("ContainerID"), OracleType.VarChar)
        insertpara(2) = DatabaseHelper.NewParameter(":CHILDCONTAINERNAME", para("ContainerName"), OracleType.VarChar)
        If para.ContainsKey("ContainerStepWorkReportId") Then
            insertpara(3) = DatabaseHelper.NewParameter(":containerstepworkreportid", para("ContainerStepWorkReportId"), OracleType.VarChar)
        Else
            insertpara(3) = DatabaseHelper.NewParameter(":containerstepworkreportid", "", OracleType.VarChar)
        End If
        Dim row As Integer = DatabaseHelper.ExecuteSql(strsql, insertpara)
        If row > 0 Then
            AddChildContainerdetailInfo = True
        End If
    End Function

    ''' <summary>
    ''' 根据分批唯一ID查询已存的零件信息
    ''' </summary>
    ''' <param name="ContainerSplitInfoID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function SelectChildContainerDetailInfo(ByVal ContainerSplitInfoID As String) As DataTable Implements IBusiness.uLMSSplitIBusiness.SelectChildContainerDetailInfo
        Dim strsql As String = String.Format("SELECT C.CHILDCONTAINERID AS CONTAINERID,C.CHILDCONTAINERNAME AS CONTAINERNAME FROM CONTAINERSPLITINFODETAIL C WHERE C.CONTAINERSPLITINFOID='{0}'", ContainerSplitInfoID)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        Return dt
    End Function

    ''' <summary>
    ''' 获取批次当前序的报工信息
    ''' </summary>
    ''' <param name="containerID"></param>
    ''' <returns></returns>
    ''' <remarks>TEAMID如果为空（工区长），则为0，改派时才能获取值</remarks>
    Function GetWorkReportInfo(ByVal containerID As String) Implements IBusiness.uLMSSplitIBusiness.GetWorkReportInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CCI.CONTAINERID,CCI.CONTAINERNAME,CCI.WORKFLOWSTEPID,CCI.WORKFLOWSTEPNAME,CSW.CONTAINERSTEPWORKREPORTID,CSW.QTY,CSW.QTY2,U.UOMNAME,")
        strsql.AppendLine("NVL(CSW.LOSSQTY, 0) LOSSQTY,DECODE(CSW.CHECKTYPE, 1, '是', 2, '否') ISFIRSTCHECK,DECODE(CCO.CONTAINERCHECKINFOID, NULL, '否', '是') ISCHECKED,")
        strsql.AppendLine("NVL(CSW.TEAMID,0) TEAMID,CSW.WORKFLOWSTEPID,CSW.RESOURCEID,CSW.CHECKERID,CSW.FACTORYID,CSW.WORKCENTERID,CSD.CONTAINERSTEPDISPATCHID,")
        strsql.AppendLine("CSD.PRIORITYCODE,CSW.WORKERID,EM.FULLNAME,CCO.CONTAINERCHECKINFOID,CSD.QTY DISQTY,CSD.PLANNEDSTARTDATE,CSD.PLANNEDFINISHDATE,CSW.CREATEDATE,")
        strsql.AppendLine("EM2.FULLNAME SURPLUSEMPLOYEENAME,CSD.EMPLOYEEID,CSW.CHECKTYPE")
        strsql.AppendLine("FROM CONTAINERSTEPWORKREPORT CSW ")
        strsql.AppendLine("LEFT JOIN CONTAINERCURRENTINFO CCI ON CCI.CONTAINERID = CSW.CONTAINERID")
        strsql.AppendLine("LEFT JOIN CONTAINERCHECKINFO CCO ON CCO.CONTAINERSTEPWORKREPORTID = CSW.CONTAINERSTEPWORKREPORTID AND CCO.ISUSE = 0")
        strsql.AppendLine("LEFT JOIN CONTAINERSTEPDISPATCH CSD ON CSD.CONTAINERSTEPDISPATCHID = CSW.CONTAINERSTEPDISPATCHID AND CSD.ISUSE = 0")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = CSW.WORKERID ")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM2 ON EM2.EMPLOYEEID = CSW.SURPLUSEMPLOYEEID ")
        strsql.AppendLine("LEFT JOIN UOM U ON U.UOMID = CSW.UOM2ID ")
        strsql.AppendLine("WHERE CSW.CONTAINERSTEPWORKREPORTID IS NOT NULL AND CCI.WORKFLOWSTEPID = CSW.WORKFLOWSTEPID AND CSW.ISUSE = 0")
        strsql.AppendFormat(" AND CCI.WORKFLOWSTEPID = CSW.WORKFLOWSTEPID AND CSW.QTY > NVL(CSW.LOSSQTY, 0) AND CCI.CONTAINERID ='{0}'", containerID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 更新拆分批次报工表信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateWorkReportInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSSplitIBusiness.UpdateWorkReportInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE CONTAINERSTEPWORKREPORT CSW SET ")
        If para.ContainsKey("ContainerID") Then
            strsql.AppendFormat(" CSW.CONTAINERID ='{0}'", para("ContainerID"))
            strsql.AppendLine(", CSW.CONTAINERSTEPDISPATCHID = NULL")
            If para.ContainsKey("changeistransfer") Then
                strsql.AppendLine(",csw.istransfer=0 ") 'add by tianFW 2018年5月25日
            End If
        Else
            strsql.AppendFormat(" CSW.QTY = '{0}'", para("Qty"))
        End If
        strsql.AppendFormat("WHERE CSW.CONTAINERSTEPWORKREPORTID = '{0}'", para("ContainerStepWorkreportID"))
        If DatabaseHelper.ExecuteSql(strsql.ToString) > 0 Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' 更新汇总表中的检验序标识（首检或工序检）
    ''' </summary>
    ''' <param name="contaienrid">批次ID</param>
    ''' <param name="type">1 首检 2 工序检</param>
    ''' <param name="value">0检验序标识 0 1 2</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateCurrentInfoCheckNum(ByVal contaienrid As String, ByVal type As Integer, ByVal value As String) As Boolean Implements IBusiness.uLMSSplitIBusiness.UpdateCurrentInfoCheckNum
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE CONTAINERCURRENTINFO C SET")
        If type = 1 Then '首检
            strsql.AppendFormat(" C.FirstcheckNum = {0}", value)
        ElseIf type = 2 Then '工序检
            strsql.AppendFormat(" C.StepcheckNum = {0}", value)
        End If
        strsql.AppendFormat(" WHERE C.CONTAINERID = '{0}'", contaienrid)
        If DatabaseHelper.ExecuteSql(strsql.ToString) > 0 Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' 更新检验表中的ContainerID信息
    ''' </summary>
    ''' <param name="containercheckinfoID"></param>
    ''' <param name="containerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateContainerCheckInfo(ByVal containercheckinfoID As String, ByVal containerid As String) As Boolean Implements IBusiness.uLMSSplitIBusiness.UpdateContainerCheckInfo
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE CONTAINERCHECKINFO C SET C.CONTAINERID = '{0}' WHERE C.CONTAINERCHECKINFOID = '{1}'", containerid, containercheckinfoID)
        If DatabaseHelper.ExecuteSql(strsql.ToString) > 0 Then
            strsql.Clear()
            'try catch --防止检验记录没有工时记录出错
            Try
                strsql.AppendFormat("UPDATE CONTAINERSTEPTIMESHEET C SET C.CONTAINERID = '{0}' WHERE C.CONTAINERCHECKINFOID = '{1}'", containerid, containercheckinfoID)
                If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
                    Return True
                End If
            Catch ex As Exception
                Return True
            End Try
        End If
        Return False
    End Function

    ''' <summary>
    ''' 更新汇总表中报工数
    ''' </summary>
    ''' <param name="qty"></param>
    ''' <param name="containerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateContianercurrentInfoWorkReportNum(ByVal qty As String, ByVal containerid As String) As Boolean Implements IBusiness.uLMSSplitIBusiness.UpdateContianercurrentInfoWorkReportNum
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE CONTAINERCURRENTINFO C SET C.WORKREPORTNUM = {0} ", qty)
        strsql.AppendFormat(" WHERE C.CONTAINERID = '{0}'", containerid)
        If DatabaseHelper.ExecuteSql(strsql.ToString) > 0 Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' 根据报工ID获取已经报工的子序号信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerStepWorkReportChildInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSSplitIBusiness.GetContainerStepWorkReportChildInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSWC.CHILDCONTAINERID CONTAINERID,C.CONTAINERNAME,CSWC.CONTAINERSTEPWORKREPORTID FROM CONTAINERSTEPWORKREPORTCHILD CSWC")
        strsql.AppendLine("LEFT JOIN CONTAINER C ON C.CONTAINERID = CSWC.CHILDCONTAINERID")
        If para.Keys.Contains("ContainerStepWorkreportID") Then
            strsql.AppendFormat(" WHERE CSWC.CONTAINERSTEPWORKREPORTID = '{0}' AND C.STATUS=1", para("ContainerStepWorkreportID"))
        End If
        strsql.AppendLine(" ORDER BY CSWC.CHILDCONTAINERID")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 根据子序号更新报工信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateContainerStepWorkReportChild(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSSplitIBusiness.UpdateContainerStepWorkReportChild
        Dim strsql = String.Format("UPDATE CONTAINERSTEPWORKREPORTCHILD CCD SET CCD.CONTAINERSTEPWORKREPORTID = '{0}' WHERE CCD.CHILDCONTAINERID = '{1}'", para("ContainerStepWorkReportID"), para("ContainerID"))
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' 根据派工单ID更新派工单数量
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateContainerStepDispatchInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSSplitIBusiness.UpdateContainerStepDispatchInfo
        'Dim strsql As String = String.Format("UPDATE CONTAINERSTEPDISPATCH CSD SET CSD.QTY = CSD.QTY - {0} WHERE CSD.CONTAINERSTEPDISPATCHID = '{1}'", para("SplitQty"), para("ContainerStepDispatchID"))
        'If DatabaseHelper.ExecuteSql(strsql) > 0 Then
        '    Return True
        'End If
        'Return False
        Dim strsql As New StringBuilder
        strsql.AppendFormat("SELECT CSD.QTY,(CSD.QTY - {0}) AS LASTQTY,NVL(A.REPORTQTY,0) REPORTQTY FROM CONTAINERSTEPDISPATCH CSD ", para("SplitQty"))
        strsql.AppendLine("LEFT JOIN (SELECT SUM(CSW.QTY) REPORTQTY, CSW.CONTAINERSTEPDISPATCHID FROM CONTAINERSTEPWORKREPORT CSW")
        strsql.AppendFormat(" WHERE CSW.CONTAINERSTEPDISPATCHID = '{0}' AND CSW.ISUSE=0 ", para("ContainerStepDispatchID"))
        strsql.AppendLine("GROUP BY CSW.CONTAINERSTEPDISPATCHID) A")
        strsql.AppendLine("ON A.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID")
        strsql.AppendFormat("WHERE CSD.CONTAINERSTEPDISPATCHID='{0}' AND CSD.ISUSE = 0", para("ContainerStepDispatchID"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
        Dim strsql_ As String = ""
        Dim isDel As Boolean = False
        '如果派工数减去拆分数不大于0，则进行处理（当之前派工100，后来批次在制数变120，则会出现可以报120，拆110或120出去，则就有派工数小于拆分数）
        If Integer.Parse(dt.Rows(0)("LASTQTY")) <= 0 Then
            '因为每条可能涉及的报工数拆分后都会将对应派工ID清空，所有根据派工ID查出来的报工总数都是最新的，如果报工数等于拆分数，则说明全部报工都拆分了。
            '如果报工数还大于拆分数则说明是派工100,在制数120，拆分110，还有10没有拆分，所以要保留10报工记录的派工记录。因此将该派工记录派工数置为0
            'dt.Rows(0)("REPORTQTY")是处理的报工记录后剩余的派工记录报工记录总数，如果它大于0，则需要保留该报工记录的派工记录，派工记录派工数置为0
            If Integer.Parse(dt.Rows(0)("REPORTQTY")) <= 0 Then
                isDel = True
            Else
                strsql_ = String.Format("UPDATE CONTAINERSTEPDISPATCH CSD SET CSD.QTY = 0 WHERE CSD.CONTAINERSTEPDISPATCHID='{0}'", para("ContainerStepDispatchID"))
            End If
        Else
            strsql_ = String.Format("UPDATE CONTAINERSTEPDISPATCH CSD SET CSD.QTY = CSD.QTY - {0} WHERE CSD.CONTAINERSTEPDISPATCHID = '{1}'", para("SplitQty"), para("ContainerStepDispatchID"))
        End If
        If isDel Then
            Dim m_DispatchObjectBus As New uLMSDispatchBusiness
            If m_DispatchObjectBus.DelContainerStepDispatchBycsdid(para("ContainerStepDispatchID")) Then
                Return True
            End If
        Else
            If DatabaseHelper.ExecuteSql(strsql_) > 0 Then
                Return True
            End If
        End If
        Return False
    End Function

    ''' <summary>
    ''' 根据contianerid 更新汇总表中的派工标识和工序派工标识
    ''' </summary>
    ''' <param name="containerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdataContainerCurrentInfo_DispatchNum_IsDispatch(ByVal containerid As String) As Boolean Implements IBusiness.uLMSSplitIBusiness.UpdataContainerCurrentInfo_DispatchNum_IsDispatch
        Dim strsql As String = String.Format("UPDATE CONTAINERCURRENTINFO C SET C.DISPATCHNUM = 1, C.ISDISPATCH = 1 WHERE C.CONTAINERID = '{0}'", containerid)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' 根据ContainerID获取检验批次已经检验的数量等信息
    ''' </summary>
    ''' <param name="containerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerCheckInfo(ByVal containerid As String) As DataTable Implements IBusiness.uLMSSplitIBusiness.GetContainerCheckInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT NVL(A.CHECKQTY,0) CHECKQTY,C.QTY,NVL(C.ALLLOSSQTY,0) ALLLOSSQTY,C.CONTAINERNAME,NVL(C.WORKREPORTNUM,0) WORKREPORTNUM")
        strsql.AppendLine("FROM CONTAINERCURRENTINFO C")
        strsql.AppendLine("LEFT JOIN (SELECT SUM(CCI.CHECKQTY) CHECKQTY, CCI.CONTAINERID, CCI.WORKFLOWSTEPID FROM CONTAINERCHECKINFO CCI WHERE CCI.ISUSE=0")
        strsql.AppendLine("GROUP BY CCI.CONTAINERID, CCI.WORKFLOWSTEPID) A ON A.CONTAINERID = C.CONTAINERID ")
        strsql.AppendFormat("WHERE C.CONTAINERID IN('{0}') And A.WORKFLOWSTEPID = C.WORKFLOWSTEPID AND C.CONTAINERSTATUS=1", containerid)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 更新汇总表中的批次总报废数信息
    ''' </summary>
    ''' <param name="strqty"></param>
    ''' <param name="containerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateContainercurrent_Alllosstqy(ByVal strqty As String, ByVal containerid As String) As Boolean Implements IBusiness.uLMSSplitIBusiness.UpdateContainercurrent_Alllosstqy
        Dim strsql As String = String.Format("UPDATE CONTAINERCURRENTINFO C SET C.ALLLOSSQTY = C.ALLLOSSQTY - {0} WHERE C.CONTAINERID = '{1}'", strqty, containerid)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' 更新批次分批后的派工信息(更新批次ID或者数量，两种情况)
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateOldDispatch(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSSplitIBusiness.UpdateOldDispatch
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE CONTAINERSTEPDISPATCH CSD SET")
        If para.ContainsKey("Qty") Then
            strsql.AppendFormat("CSD.QTY = {0}", para("Qty"))
        Else
            strsql.AppendFormat(" CSD.CONTAINERID = '{0}'", para("ContainerID"))
        End If
        strsql.AppendFormat(" WHERE CSD.CONTAINERSTEPDISPATCHID= '{0}'", para("ContainerStepDispatchID"))
        If DatabaseHelper.ExecuteSql(strsql.ToString) > 0 Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' 根据批次和报工表中的派工单ID
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateContainerStepWorkInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSSplitIBusiness.UpdateContainerStepWorkInfo
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE CONTAINERSTEPWORKREPORT C  SET C.CONTAINERSTEPDISPATCHID='{0}'", para("ContainerStepDispatchID"))
        strsql.AppendFormat(" WHERE C.CONTAINERID = '{0}' AND C.WORKERID = '{1}'", para("ContainerID"), para("WorkerID"))
        If DatabaseHelper.ExecuteSql(strsql.ToString) > 0 Then
            Return True
        End If
        Return False
    End Function

    ''' <summary>
    ''' 获取检验信息并修改该信息 create tianFW 2017/11/03
    ''' </summary>
    ''' <param name="ContainerCheckInfoID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCheckInfoAndChangeCheckInfo(ByVal ContainerCheckInfoID As String, ByVal SplitQty As Integer) As DataTable Implements uLMSSplitIBusiness.GetCheckInfoAndChangeCheckInfo
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format("SELECT * FROM CONTAINERCHECKINFO C WHERE C.CONTAINERCHECKINFOID='{0}'", ContainerCheckInfoID))
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE CONTAINERCHECKINFO C SET C.ACCEPTANCEQTY = 0,C.CHECKQTY=C.CHECKQTY-{0}", SplitQty)
        strsql.AppendFormat(" WHERE C.CONTAINERCHECKINFOID='{0}'", ContainerCheckInfoID)
        If DatabaseHelper.ExecuteSql(strsql.ToString) Then
            Return dt
        Else
            Return New DataTable
        End If
    End Function

    ''' <summary>
    ''' 添加批次检验记录
    ''' Create:tianFW 2017/11/03
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AddContainerCheckInfo(ByVal entity As ContainerCheckInfoDTO) As Boolean Implements uLMSSplitIBusiness.AddContainerCheckInfo
        Dim strsql As String =
           <string>
                INSERT INTO CONTAINERCHECKINFO(CONTAINERID,FACTORYID,WORKFLOWSTEPID,CHECKERID,CHECKQTY,ACCEPTANCEQTY,LOSSQTY,REWORKQTY,DEFECTQTY,
                CONTAINERCHECKINFOID,CREATEDATE,CHECKNOTE,FIRSTCHECKBILLNO,CONTAINERSTEPWORKREPORTID,ISUSE,ISTRANSFER,CHECKTYPENOTE)
                VALUES(:containerid,:factoryid,:workflowstepid,:checkerid,:checkqty,:acceptanceqty,:lossqty,:reworkqty,:defectqty,
                :containercheckinfoid,sysdate,:checknote,:firstcheckbillno,:containerstepworkreportid,0,1,:CHECKTYPENOTE)
            </string>
        Dim insertpar(13) As OracleParameter

        insertpar(0) = DatabaseHelper.NewParameter(":containerid", entity.ContainerID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":factoryid", entity.FactoryID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":checkerid", entity.CheckerID, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":checkqty", entity.CheckQty, OracleType.Number)
        insertpar(5) = DatabaseHelper.NewParameter(":acceptanceqty", entity.AcceptanceQty, OracleType.Number)
        insertpar(6) = DatabaseHelper.NewParameter(":lossqty", entity.LossQty, OracleType.Number)
        insertpar(7) = DatabaseHelper.NewParameter(":reworkqty", entity.ReworkQty, OracleType.Number)
        insertpar(8) = DatabaseHelper.NewParameter(":defectqty", entity.DefectQty, OracleType.Number)
        insertpar(9) = DatabaseHelper.NewParameter(":containercheckinfoid", entity.ContainerCheckInfoID, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":checknote", entity.CheckNote, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":firstcheckbillno", entity.FirstCheckBillNo, OracleType.VarChar)
        insertpar(12) = DatabaseHelper.NewParameter(":containerstepworkreportid", entity.ContainerStepWorkReportID, OracleType.VarChar)
        insertpar(13) = DatabaseHelper.NewParameter(":CHECKTYPENOTE", entity.CheckTypeNote, OracleType.Number)

        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        End If
        Return True
    End Function

    ''' <summary>
    ''' 根据检验唯一ID获取工时记录信息 create tianFW 2017年11月20日
    ''' </summary>
    ''' <param name="ContainerCheckInfoID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepTimeInfoByCheckID(ByVal ContainerCheckInfoID As String) As DataTable Implements uLMSSplitIBusiness.GetStepTimeInfoByCheckID
        Dim strsql As String = String.Format("SELECT * FROM CONTAINERSTEPTIMESHEET CSS WHERE CSS.ISUSE=0 AND CSS.CONTAINERCHECKINFOID='{0}'", ContainerCheckInfoID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 按工时记录表唯一ID删除工时记录 create tianFW 2017年11月20日
    ''' </summary>
    ''' <param name="ContainerStepTimeSheetID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelStepTimeInfoByID(ByRef ContainerStepTimeSheetID As String) As Boolean Implements uLMSSplitIBusiness.DelStepTimeInfoByID
        Dim strsql As String = String.Format("DELETE FROM CONTAINERSTEPTIMESHEET CSS WHERE CSS.CONTAINERSTEPTIMESHEETID='{0}'", ContainerStepTimeSheetID)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取批次历史拆分记录 2017年12月25日 create tianFW 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerHistorySplitInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSSplitIBusiness.GetContainerHistorySplitInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine(" SELECT CSI.CONTAINERID,CSI.WORKFLOWSTEPID,CSI.QTY,CSI.CHECKDATA,SPLITCONTAINERNAME,CSI.SPLITRESULT,CONTAINERSPLITINFOID")
        strsql.AppendLine(" FROM CONTAINERSPLITINFO CSI")
        strsql.AppendLine(" WHERE CSI.ISUSE = 0")
        strsql.AppendFormat(" AND CSI.CONTAINERID ='{0}' AND CSI.WORKFLOWSTEPID='{1}'", para("ContainerID"), para("WorkflowStepID"))
        strsql.AppendLine("ORDER BY CSI.CreateDate DESC")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 删除批次拆分记录 2017年12月28日17:02:54
    ''' </summary>
    ''' <param name="ID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerSplitInfo(ByVal ID As String) As Boolean Implements uLMSSplitIBusiness.DelContainerSplitInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE CONTAINERSPLITINFO CSI SET CSI.ISUSE=1,CSI.ISTRANSFER=0 ")
        strsql.AppendFormat(" WHERE CSI.CONTAINERSPLITINFOID='{0}'", ID)
        If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取批次主工艺的子工序关联信息 create tianFW2018年3月8日
    ''' </summary>
    ''' <param name="containerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSubWorkflowInfoByContainerID(ByVal containerid As String) As DataTable Implements uLMSSplitIBusiness.GetSubWorkflowInfoByContainerID
        Dim strsql As New StringBuilder
        strsql.AppendFormat("SELECT * FROM SUBWORKFLOWCHECKINFO SWF WHERE SWF.CONTAINERID='{0}'", containerid)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 分批为新批次增加工艺与子工艺关联信息 create tianFW 2018年3月8日
    ''' </summary>
    ''' <param name="containerid"></param>
    ''' <param name="splitcontainerid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddSplitContainerSubWorkflowInfo(ByVal containerid As String, ByVal splitcontainerid As String) As Boolean Implements uLMSSplitIBusiness.AddSplitContainerSubWorkflowInfo
        AddSplitContainerSubWorkflowInfo = False
        Dim dt As DataTable = GetSubWorkflowInfoByContainerID(containerid)
        If dt.Rows.Count = 0 Then
            AddSplitContainerSubWorkflowInfo = True
            Exit Function
        End If
        Dim SQLlist As New Dictionary(Of String, OracleParameter())()
        Dim strsql As String =
            <string>
                INSERT INTO SUBWORKFLOWCHECKINFO(CHECKDATE,CHECKRESULT,CHECKTECHNIQUEID,CONTAINERID,CREATEDATE,CREATEEMPLOYEEID,SUBWORKFLOWCHECKINFOID,SUBWORKFLOWID,WORKFLOWID,planemployeeid,workflowstepid,qty)
                VALUES(:CHECKDATE,:CHECKRESULT,:CHECKTECHNIQUEID,:CONTAINERID,:CREATEDATE,:CREATEEMPLOYEEID,:SUBWORKFLOWCHECKINFOID,:SUBWORKFLOWID,:WORKFLOWID,:planemployeeid,:workflowstepid,:qty)
            </string>
        Dim insertpar(11) As OracleParameter
        For Each dr As DataRow In dt.Rows
            Array.Clear(insertpar, 0, 12)
            If dr.IsNull("CHECKDATE") Then
                insertpar(0) = DatabaseHelper.NewParameter(":CHECKDATE", DBNull.Value, OracleType.DateTime)
            Else
                insertpar(0) = DatabaseHelper.NewParameter(":CHECKDATE", DateTime.Parse(dr("CHECKDATE")), OracleType.DateTime)
            End If
            If dr.IsNull("CHECKRESULT") Then
                insertpar(1) = DatabaseHelper.NewParameter(":CHECKRESULT", DBNull.Value, OracleType.VarChar)
            Else
                insertpar(1) = DatabaseHelper.NewParameter(":CHECKRESULT", dr("CHECKRESULT"), OracleType.VarChar)
            End If
            If dr.IsNull("CHECKTECHNIQUEID") Then
                insertpar(2) = DatabaseHelper.NewParameter(":CHECKTECHNIQUEID", DBNull.Value, OracleType.VarChar)
            Else
                insertpar(2) = DatabaseHelper.NewParameter(":CHECKTECHNIQUEID", dr("CHECKTECHNIQUEID"), OracleType.VarChar)
            End If
            insertpar(3) = DatabaseHelper.NewParameter(":CONTAINERID", splitcontainerid, OracleType.VarChar)
            If dr.IsNull("CREATEDATE") Then
                insertpar(4) = DatabaseHelper.NewParameter(":CREATEDATE", DBNull.Value, OracleType.DateTime)
            Else
                insertpar(4) = DatabaseHelper.NewParameter(":CREATEDATE", DateTime.Parse(dr("CREATEDATE")), OracleType.DateTime)
            End If
            If dr.IsNull("CREATEEMPLOYEEID") Then
                insertpar(5) = DatabaseHelper.NewParameter(":CREATEEMPLOYEEID", DBNull.Value, OracleType.VarChar)
            Else
                insertpar(5) = DatabaseHelper.NewParameter(":CREATEEMPLOYEEID", dr("CREATEEMPLOYEEID"), OracleType.VarChar)
            End If
            insertpar(6) = DatabaseHelper.NewParameter(":SUBWORKFLOWCHECKINFOID", System.Guid.NewGuid().ToString(), OracleType.VarChar)
            insertpar(7) = DatabaseHelper.NewParameter(":SUBWORKFLOWID", dr("SUBWORKFLOWID"), OracleType.VarChar)
            insertpar(8) = DatabaseHelper.NewParameter(":WORKFLOWID", dr("WORKFLOWID"), OracleType.VarChar)
            If dr.IsNull("planemployeeid") Then
                insertpar(9) = DatabaseHelper.NewParameter(":planemployeeid", DBNull.Value, OracleType.VarChar)
            Else
                insertpar(9) = DatabaseHelper.NewParameter(":planemployeeid", dr("planemployeeid"), OracleType.VarChar)
            End If
            If dr.IsNull("workflowstepid") Then
                insertpar(10) = DatabaseHelper.NewParameter(":workflowstepid", DBNull.Value, OracleType.VarChar)
            Else
                insertpar(10) = DatabaseHelper.NewParameter(":workflowstepid", dr("workflowstepid"), OracleType.VarChar)
            End If
            If dr.IsNull("qty") Then
                insertpar(11) = DatabaseHelper.NewParameter(":qty", DBNull.Value, OracleType.Number)
            Else
                insertpar(11) = DatabaseHelper.NewParameter(":qty", dr("qty"), OracleType.Number)
            End If
            DatabaseHelper.ExecuteSql(strsql, insertpar)
        Next
        AddSplitContainerSubWorkflowInfo = True
    End Function

    ''' <summary>
    ''' 更新批次分批状态标识
    ''' </summary>
    ''' <param name="containerID">批次ID</param>
    ''' <param name="type">更新状态0 无 1 有</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateSplitNum(ByVal containerID As String, ByVal type As Integer) As Boolean Implements uLMSSplitIBusiness.UpdateSplitNum
        Dim strsql As New StringBuilder
        strsql.AppendFormat("UPDATE CONTAINERCURRENTINFO CU SET CU.SPLITNUM = {0} WHERE CU.CONTAINERID = '{1}'", type, containerID)
        If DatabaseHelper.ExecuteSql(strsql.ToString()) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function
End Class
